package cn.com.xuhx.testService;

import cn.com.xuhx.pojo.User;
import cn.com.xuhx.service.UserService;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import java.util.Map;

/**
 * 查询条件构造器
 *  - 可用于查询、删除、修改
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class QueryWrapperTest {

    @Autowired
    private UserService userService;

    /**
     * 条件查询
     */
    @Test
    public void queryWrapper(){
        //SELECT id,name,age,address,tel,sfzh,bornday,delStatus FROM T_USERS WHERE delStatus=0 AND (name LIKE ? AND age BETWEEN ? AND ? AND tel IS NOT NULL) ORDER BY age DESC
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.likeRight("name","李")
                .between("age",15,20)
                .isNotNull("tel").orderByDesc("age");
        List<User> users = userService.list(wrapper);
        System.out.println(users);
    }

    /**
     * 条件删除
     */
    @Test
    public void delWrapper(){
        //UPDATE T_USERS SET delStatus=1 WHERE delStatus=0 AND (id BETWEEN ? AND ?)
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.between("id",7L,9L);
        boolean remove = userService.remove(wrapper);
        System.out.println(remove);
    }

    /**
     * 条件修改
     */
    @Test
    public void updateWrapper(){
        //UPDATE T_USERS SET address=?, tel=?, sfzh=? WHERE delStatus=0 AND (id = ?)
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("id",12L);
        User user = new User();
        user.setAddress("魔仙堡");
        user.setTel("19890991788");
        user.setSfzh("320655432107081527");
        boolean update = userService.update(user, wrapper);
        System.out.println(update);
    }

    /**
     * 复杂逻辑条件查询
     */
    @Test
    public void queryFZ(){
        //SELECT id,name,age,address,tel,sfzh,bornday,delStatus FROM T_USERS WHERE delStatus=0 AND (sfzh LIKE ? AND (age BETWEEN ? AND ? OR name LIKE ?))
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        wrapper.likeRight("sfzh","3206")
                .and((w) -> {
            w.between("age",15,20)
                    .or()
                    .likeRight("name","纪");
        });
        List<User> list = userService.list(wrapper);
        System.out.println(list);
    }

    /**
     * 查询自定义字段
     */
    @Test
    public void queryDefineCloums(){
        //SELECT name,age,address FROM T_USERS WHERE delStatus=0
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select("name","age","address");
        List<Map<String, Object>> maps = userService.listMaps(wrapper);
        System.out.println(maps);
    }

    /**
     * 模拟开发环境
     */
    @Test
    public void querySD(){
        //SELECT id,name,age,address,tel,sfzh,bornday,delStatus FROM T_USERS WHERE delStatus=0 AND (age >= ?)
        //模拟前台传递的数据
        String username = null;
        Integer startAge = 10;
        Integer endAge = null;

        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like(StringUtils.isNotBlank(username),"name",username);
        wrapper.ge(startAge != null,"age",startAge);
        wrapper.le(endAge != null,"age",endAge);
        List<User> list = userService.list(wrapper);
        System.out.println(list);
    }

    /**
     * 使用lambda模拟开发环境，可以有效防止我们写错字段名称（Lambda是jdk1.8新特性，所以需要1.8以上的环境支持）
     */
    @Test
    public void querySDByLambda(){
        //SELECT id,name,age,address,tel,sfzh,bornday,delStatus FROM T_USERS WHERE delStatus=0 AND (age >= ?)
        String username = null;
        Integer startAge = 10;
        Integer endAge = null;

        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        wrapper.like(StringUtils.isNotBlank(username),User::getName,username);
        wrapper.ge(startAge != null,User::getAge,startAge);
        wrapper.le(endAge != null,User::getAge,endAge);

        List<User> list = userService.list(wrapper);
        System.out.println(list);
    }
}
